Hello everyone.
Welcome to this lecture on Pivot Tables in excel they were developed very strong tools which are used
to summarize large datasets there were tables automatically grouped together similar materials to give
out a meaningful summary to understand how.
Let us look at this funny tattoos his data for solid wood works company in the first column.
We have salesperson name I'd give them a salesperson one to entry into one then in the product or name
the next column contains these region in which the sale was made.
Next is customer name then date then number of units bought by the customer.
Then a day per unit cost.
On the next one is total payment made by the customer.
Now let us say we want to find out the sales made by each sales person.
One way to do this is to apply for that one by one and some sales by each is person
so we have applied a filter on salesperson 1 and the sum of said is about 2 million but doing this will
be very time taking and cumbersome instead.
Let's do this using the Red Devils so we go to the Insert menu click on Spirit devil
in the first text but please ensure that the entire table area selected with excellent selected automatically
foreach is next.
If you want the pivot table in the same location.
Select the single cute option I wanted and in the new.
So select.
Okay you can see that a new sheet is added to the file.
On the right is the pivot table menu.
Notice that it has all the column names listed below this list are for small boxes you can just drag
and drop the desired column from above in the respective box together customize reports.
If a drag drop salesperson in the rows all these sales personnel listed on the left.
Similarly if I drag and drop the region name two columns all four regions will be listed in columns
we can apply if it does by using different looks but no let us try to get the sales summary of every
sales person we will drag and drop the sales person indeed rows box then we will bankrupt total sales
value in the values book
notice that this is showing some of total cost in the values box which means it has automatically added
the sales value wherever salesperson or sales person 1 or sales percent do and so on next to identify
which customer this is person has sold how much amount of furniture we will pick up customer name and
we'll drop it into the columns box we can see each is person sale to each customer and the value of
it.
But if we want to see the count of sales that it is worth and has done we will go to the value box click
on value field settings and instead of some we will select.
Column on okay.
You will see that salesperson 1 has made 17 sales to furniture and 11 to student Nicole overall as body
count salesperson 3 has made 119 sales which is the largest sales to arrange this data that is to sort
this data from largest to smallest.
You go to the grand total column right click on any value and select sort largest to smallest you can
see that 6 percent grade is on top with the maximum number of sales and salesperson 7 is at the bottom
with the least number of sales there's one more box left which is difficult box if you want to play
something that say for example if I want to figure that out some particular regions from my data I'll
pick the regions box regions options and put in different box.
You can see that it has come on the top left corner.
If you click on this small triangle you see all the four regions that are data has.
If you select Northeast only and click on okay these for salesperson and made these any sales in the
northeast region.
Similarly you can change notice to not less to see CDL the northwest northwest region.
But if you want to select multiple values in this field to take on the checkbox and then select multiple
values that you want to fill that so if we want northeast northwest anthologies.
Take those and click on okay and you will find all these persons who have sold in these three regions.
No instead of seeing this data from salesperson point of view if you want to see the amount of seed
made every month we will remove salesperson from this table.
And really bring the date column in tidy rows box
we can see
we can see that the date will group automatically by excel in two years quarters and dates if you want
to change the grouping.
You just go and click on it go to the group option and you until like years and quarters leave the month
option and click on okay.
You will now be able to see monthly sales for this furniture company next.
If you want to match the total sales made to these two companies which is home space and hometown probably
these two companies are most together and we want to grab these two sales values will select these two
and right click group and rent.
We take this minus but then see the combined sales to this group of two companies
next year these laser options like sales are similar to those only the these are more convenient to
a play so to a player as laser we go to the analyze option in the menu and in that we go to the inserts
later option inserts later is giving us the option to select the column name on which we want to play
this filter.
So for example if I want to apply for one region with a convenient and click Okay so this laser deferred
options which we are all too high.
If you click on ne we are seeing this agent noticed regionally if you click on Northwest we are seeing
this here the northwest region only if I want to select multiple regions I will click on this button
and select northeast and northwest.
But this gives us an easy way to play predators.
That's all for this lecture.
In the next lecture we will cover charts.
Thanks.